package pebom;

import java.sql.Statement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @author raphaelwb
 */
public class BD {

    private static BD banco;

    public static BD getInstance() {
        if (banco == null) {
            banco = new BD();
        }
        return banco;
    }
    private static String dbURL = "jdbc:derby:PeBom;create=true";
    // jdbc Connection
    private static Connection connect = null;
    private static Statement stat = null;

    public BD() {
        createConnection();
        dropTables();
        createTables();
    }

    public void createConnection() {
        try {
            Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
            //Get a connection
            connect = DriverManager.getConnection(dbURL);
        } catch (Exception except) {
            except.printStackTrace();
        }
    }
//tableExists
    public Connection getConn() {
        return connect;
    }
    
    public void createTables() {
        try {
            stat = connect.createStatement();
            if (!tabelaExiste("produtos")) {
                stat.execute("create table produtos (id int NOT NULL auto_increment , nome VARCHAR(50) NOT NULL, descricao VARCHAR(50) NOT NULL, PRIMARY KEY(id))");
            }
            if (!tabelaExiste("clientes")){
                stat.execute("create table clientes (cpf int NOT NULL , nome VARCHAR(50) NOT NULL, PRIMARY KEY(cpf))");
            }
            if (!tabelaExiste("vendas")) {
                stat.execute("create table vendas (id_produto int NOT NULL, id_cliente int NOT NULL, data DATE NOT NULL, quantidade int NOT NULL, FOREIGN KEY(id_produto) references produtos(id)  ,FOREIGN KEY(id_cliente) references clientes(cpf) , CONSTRAINT PK_Objeto PRIMARY KEY (id_produto, id_cliente))");
            }
            
            stat.close();
        } catch (SQLException sqlExcept) {
            sqlExcept.printStackTrace();
        }
    }


    public boolean tabelaExiste(String tabela) {
        boolean tableExists = false;
        ResultSet result;
        DatabaseMetaData metadata = null;

        try {
            metadata = connect.getMetaData();

            result = metadata.getTables(null, null, null, null);
            while ( result.next()){
                if ( result.getString("TABLE_NAME").equals(tabela))
                    tableExists = true;
            }
        } catch (java.sql.SQLException e) {
            e.printStackTrace();
        }
        return tableExists;
    }

    public void dropTables() {
        try {
            stat = (Statement) connect.createStatement();
            /*if (!tabelaExiste("USUARIO")) {
                stmt.execute("DROP TABLE usuario");
            }*/
            if (!tabelaExiste("produtos")) {
                stat.execute("DROP TABLE produtos");
            }
             if (!tabelaExiste("Vendas")) {
                stat.execute("DROP TABLE vendas");
            }
             if (!tabelaExiste("Clientes")) {
                stat.execute("DROP TABLE clientes");
            }
           stat.close();
        } catch (SQLException sqlExcept) {
        }
    }
}
